/**
 * Copyright (C)  2011  chen wei hua.
 * Permission is granted to copy, distribute and/or modify this document
 * under the terms of the GNU Free Documentation License, Version 2.0
 * or any later version published by the Free Software Foundation;
 * with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.
 * A copy of the license is included in the section entitled "GNU Free Documentation License".
 */
package com.javaeye.common.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.javaeye.common.dto.Exportable;

public class ExcelUtil {
	
	public static final String EXCEL_SUFF = ".xls";
	
	/* 下面这个方法是将list转换为Excel工作表的 */
	public static Workbook getWorkbook(List<Exportable> list) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		
		// 首先，我们读取list中的第一个元素，根据它来确定工作表的列名，以及输出数据所对应的方法数组
		Exportable exp = list.get(0);
		int columnCount = exp.getColumnCount();

		HSSFRow row = sheet.createRow(0); // 创建第1行，也就是输出表头
		HSSFCell cell;
		for (int i = 0; i < columnCount; i++) {
			cell = row.createCell(i); // 创建第i列
			cell.setCellValue(new HSSFRichTextString(exp.getColumnName(i)));
		}

		// 下面是输出各行的数据
		for (int i = 0; i < list.size(); i++) {
			exp = (Exportable) list.get(i);
			row = sheet.createRow(i + 1);// 创建第i+1行
			for (int j = 0; j < columnCount; j++) {
				cell = row.createCell(j);// 创建第j列
				Object obj = exp.getValue(j);
				String value = (obj==null ? "" : obj.toString());
				cell.setCellValue(value);
			}
		}
		return workbook;
	}
	
	public static ByteArrayInputStream workbook2InputStream(Workbook workbook) throws Exception {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        baos.flush();
        byte[] aa = baos.toByteArray();
        ByteArrayInputStream excelStream = new ByteArrayInputStream(aa, 0, aa.length);
        baos.close();
        return excelStream;
	}
	
    public static Workbook createWorkBook(File file) throws Exception {
    	//String filePath = file.getAbsolutePath();
    	FileInputStream is = new FileInputStream(file);
    	Workbook book = new HSSFWorkbook(is);  
        is.close();
        return book;  
    }
    
    public static double getNumber(Row ros, int index) {
		Cell cell = ros.getCell(index);
		
		if (cell == null) {
			return 0;
		}
		int type = cell.getCellType();
		if (type == HSSFCell.CELL_TYPE_STRING) {
			String cellValue = cell.getStringCellValue();
			return Double.parseDouble(cellValue);
		}
		return cell.getNumericCellValue();
	}
	
    public static String getString(Row ros, int index) {
		Cell cell = ros.getCell(index);
		if (cell == null) {
			return "";
		}
		return cell.getStringCellValue();
	}
    
    public static void setCellValue(Sheet sheet, int rolIndex, int colIndex, Object value) {
		Row ros = sheet.getRow(rolIndex);
		HSSFCell cell = (HSSFCell)ros.getCell(colIndex);
		cell.setCellValue(value == null ? "" : value.toString());
	}

}
